# mysql 中的锁

# 1. 什么是锁?

锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用外,数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发性能的一个重要因素。

MySQL中为了保证数据访问的一致性与有效性等功能,实现了锁机制,MySQL中的锁是在服务器层或者存储引擎层实现的。
1
2
3
4
5

# 1.1 按照粒度分类

  • 全局锁:锁定数据库中的所有表
  • 表级锁:每次操作锁住整张表
  • 行级锁:每次操作锁住对应的行数据

# 2. 锁的基本内容

  • DML 语句
-- 插入操作
insert into test1 values(1, '1');

-- 更新操作
update test1 set name='2' where id=1;

-- 删除操作
delete from test1 where xx = x;
1
2
3
4
5
6
7
8
  • DDL 语句
-- 表的创建语句
create table test1 (id int primary key, name varchar(20));

-- 表的修改语句
rename table test1 to test2;

-- 表结构修改语句
alter table ...;
1
2
3
4
5
6
7
8
  • DQL 语句
-- 当前读
select * from test1 for update;
1
2
  • DCL 语句
-- 创建用户
create user '$account'@'%' identified by '$password';

-- 删除用户
drop user '$account'@'%';

-- 授权命令
grant all privileges on *.* to '$account'@'%' with grant option;

-- 撤销权限
revoke all privileges on *.* from 'account'@'%';
1
2
3
4
5
6
7
8
9
10
11
# 2.1 全局锁

对整个数据库实例进行加锁(所有的库下的所有的表),加锁成功后整个数据库实例处于只读状态

-- 加锁
flush tables with read lock;

-- 解锁
unlock tables;
1
2
3
4
5

因此,一切会对数据库数据产生修改的操作都会被阻塞(获取最新数据的当前读也会阻塞)包括但是不限于DDL、DML语句

  • 锁的基本使用
-- 使用全局锁,主要是完成对数据的备份,使用以下mysql提供的工具完成备份
cmd> mysqldump -h $host -u$user -p$password $database > $back_file_path 

-- 加锁成功
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

-- 更新处于阻塞状态
mysql> update tb_user set age = 99 where id = 1;

--  解锁成功
mysql> update tb_user set age = 99 where id = 1;

-- 更新语句成功执行,阻塞了37.93 sec
mysql> update tb_user set age = 99 where id = 1;
Query OK, 1 row affected (37.93 sec)
Rows matched: 1  Changed: 1  Warnings: 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  • 特点
在数据库中加全局锁,是一个比较重的操作,存在以下问题:
    1) 如果在主库上备份,那么备份期间都不能更新,业务基本停止.
    2) 如果在从库上备份,那么备份期间就不能执行从主库同步过来的binlog,会导致主从延迟.
    3) 在Innodb引擎中,我们可以在备份时加上参数 --single-transaction来完成不加锁的数据一致性备份. (Innodb底层使用过快照读的方式完成备份的)
1
2
3
4
Last Updated: 5/5/2023, 2:33:10 AM